<?php
    define("EXCEL_DIR", WWW_ROOT.'files'.DS.'excel');
    define("HELP_DIR", WWW_ROOT.'files'.DS.'help');
/*
 * Reports Controller class
 * PHP versions 5.1.4
 * @filesource
 * @author   Manisha/Reetu
 * @version 0.0.1 
 *   - Initial release 
 */

class ReportsController extends AppController {
    public $uses = array('Payable', 'Payee', 'User');
    
    /* __________________________________________________________
     * @Method      :beforeFilter
     * @Description :to set up the Auth component
     * @access      :null
     * @param      :null
     * @return     :null
     */
    function beforeFilter() {
        ini_set('memory_limit', '512M');
        //--------- Directory Creation------------
        if (!is_dir(EXCEL_DIR)) {
            mkdir(EXCEL_DIR, 0777);         
        }
        if (!is_dir(HELP_DIR)) {
            mkdir(HELP_DIR, 0777);         
        }
        $this->layout = 'home';
        parent::beforeFilter();
    }
    
        /* __________________________________________________________
     * @Method      :checkPayable
     * @Description :to provide a Payable sorting interface
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function checkPayable($Month = null, $Year = null, $switch = null) {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');

        if ($this->request->data) {
            $Month = $this->request->data['Report']['month'];
            $Year = $this->request->data['Report']['year'];
            //$mpf = $this->request->data['Report']['mpf'];
        } else {
            $Month = date('m');
            $Year = date('Y');
            //$mpf = 'Y';
        }
        $mpf = '';
        
        $this->set('Month', $Month);
        $this->set('Year', $Year);
        $this->set('mpf', $mpf);

        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'reports';
            $this->render('listnew', 'ajax');
        } 
    }
    
    /* __________________________________________________________
     * @Method      :showDetails
     * @Description :to provide a Payable sorting interface
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function showDetails($Month = null, $Year = null, $switch = null, $mpf = null) {
        
        if (!$Month || !$Year) {
            echo "Wrong Values Passed";
            die;
        }

        $filterDate = $filterDateMin = $filterDateMax = '';
        $filterDate = $Year.'-'.$Month;
        $maxDate = date("t", strtotime($filterDate));
        $filterDateMin = $Year.'-'.$Month.'-1';
        $filterDateMax = $Year.'-'.$Month.'-'.$maxDate;
            
            
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->unBindModel(array('belongsTo' => array('BatchList')), false);
        $this->Payable->bindModel(array(
            'hasOne' => array(
                'Payee' => array(
                    'foreignKey' => false,
                    'conditions' => 'Payee.payee_id_proof_no = Payable.payee_id_proof_no'
                )
            )
        ), false);
        
        $textLabel = '';
        switch ($switch) {
            case 1:
                $this->Payable->virtualFields = array('count' => 'COUNT(DISTINCT Payable.bank_account_no)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                    'group' => array('Payable.payee_id_proof_no HAVING COUNT(DISTINCT Payable.bank_account_no) > 1'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('BANK_ACCOUNT', true);
                break;
            case 2:
                $this->Payable->virtualFields = array('count' => 'COUNT(DISTINCT Payable.e_name)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                    'group' => array('Payable.payee_id_proof_no HAVING COUNT(DISTINCT Payable.e_name) > 1'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('ENAME', true);
                break;
            case 3:
                $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payee_id_proof_no)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                    'group' => array('Payable.payee_id_proof_no HAVING COUNT(Payable.payee_id_proof_no) > 1'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('PAYABLES', true);
                break;
            case 4:
                //$this->Payable->virtualFields = array('count' => 'COUNT(Payable.payee_id_proof_no)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no'),
                    'group' => array('Payable.payee_id_proof_no'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('ADDED_PAYEE', true);
                break;
            case 5:
                //$this->Payable->virtualFields = array('count' => 'COUNT(Payable.on_hold)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no'),
                    'group' => array('Payable.payee_id_proof_no HAVING SUM(Payable.on_hold) > 0'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('REMOVED_PAYEE', true);
                break;
            case 6:
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                   'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no'),
                   'group' => array('Payable.payee_id_proof_no'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('MPF_PAYEE', true);
                break;
            case 7:
                $this->Payable->virtualFields = array('count' => 'COUNT(Payee.joining_date)');
                $this->Paginator->settings = array(
                    'contain' => 'Payee',
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payee.joining_date' => '0000-00-00',
                    ),
                   'fields' => array('Payee.id', 'Payee.joining_date', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                   'group' => array('Payable.payee_id_proof_no'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('PAYEE_WITHOUT_JOINING', true);
                break;
            case 8:
                $this->Payable->virtualFields = array('count' => 'COUNT(Payee.bank_account_no)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payee.bank_account_no' => NULL,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                    'group' => array('Payable.payee_id_proof_no'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $textLabel = __('EMPTY_BANK_ACCOUNT', true);
                break;
        }
        
        $mpfLabel = 'Both';
        if($mpf == 'Y'){$mpfLabel = 'Yes';}
        if($mpf == 'N'){$mpfLabel = 'No';}
        
        $this->set('mpf', $mpf);
        $this->set('mpfLabel', $mpfLabel);
        $this->set('textLabel', $textLabel);
        $this->set('switch', $switch);
        $payableList = $this->Paginator->paginate('Payable');
        $this->set('payableList', $payableList);
        //pr($payableList);
        $this->set('Month', $Month);
        $this->set('Year', $Year);
        
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'reports';
            $this->render('show_details', 'ajax');
        }
    }
    
    /* __________________________________________________________
     * @Method      : calculations
     * @Description :to provide a Payable sorting interface
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function calculations($Month = null, $Year = null, $switch = null, $mpf = null) {
        
        if (!$Month || !$Year) {
            echo "Wrong Values Passed";
            die;
        }

        $filterDate = $filterDateMin = $filterDateMax = '';
        $filterDate = $Year.'-'.$Month;
        $maxDate = date("t", strtotime($filterDate));
        $filterDateMin = $Year.'-'.$Month.'-1';
        $filterDateMax = $Year.'-'.$Month.'-'.$maxDate;
            
            
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->unBindModel(array('belongsTo' => array('BatchList')) ,false);
        $this->Payable->bindModel(array(
            'hasOne' => array(
                'Payee' => array(
                    'foreignKey' => false,
                    'conditions' => 'Payee.payee_id_proof_no = Payable.payee_id_proof_no'
                )
            )
        ), false);
        
        
        $textLabel = '';
        switch ($switch) {
            case 1:
                $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payment_type_id)', 'total_payable' => 'SUM(Payable.amount_payable)');
                $condition = array(
                                    'Payable.created >= ' => $filterDateMin,
                                    'Payable.created <= ' => $filterDateMax,
                                    'Payable.payment_type_id' => array(2, 3, 4),
                                    'Payable.on_hold' => 0,
                                );
                if($mpf){ $condition['Payable.mpf_entitlement'] = $mpf;}
                $paymentTypeIDData = $this->Payable->find('all', array(
                                'conditions' => $condition,
                                'fields' => array('Payable.count', 'Payable.total_payable', 'Payable.payee_id_proof_no', 'Payable.payment_type_id'),
                                'group' => array('Payable.payment_type_id', 'Payable.payee_id_proof_no'),
                                'order' => array('Payable.payee_id_proof_no'),
                            ));
                
                $paymentData = array();
                if(!empty($paymentTypeIDData)){
                    foreach ($paymentTypeIDData as $data){
                        $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['count'] = $data['Payable']['count'];
                        $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['total_payable'] = $data['Payable']['total_payable'];
                    }
                }
                $this->set('paymentData', $paymentData);
                
                $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payee_id_proof_no)', 'total_payable' => 'SUM(Payable.amount_payable)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payee.joining_date', 'Payable.payee_id_proof_no', 'Payable.count', 'Payable.bank_account_no', 'Payable.total_payable', 'Payable.mpf_entitlement'),
                    'group' => array('Payable.payee_id_proof_no'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                
                $textLabel = __('MPF_N_PAYMENT', true);
                if($mpf == 'N'){
                    $element_to_render = 'calculations_no_mpf';
                } else {
                    $element_to_render = 'calculations';
                }
                break;
            case 2:
                $mpf_plus_thirty = date('Y-m-d', strtotime($filterDateMin . " -1 month"));
                $join_date_min = date('Y-m-d', strtotime($mpf_plus_thirty . " -30 day"));
                $join_date_month = date('m', strtotime($join_date_min));
                $join_date_year = date('Y', strtotime($join_date_min));
                $join_day_max = date("t", strtotime($join_date_year . '-' . $join_date_month));
                $join_date_max = $join_date_year . '-' . $join_date_month . '-' . $join_day_max;
                
                $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payment_type_id)', 'total_payable' => 'SUM(Payable.amount_payable)');
                $condition = array(
                                    'Payee.joining_date >= ' => $join_date_min,
                                    'Payee.joining_date <= ' => $join_date_max,
                                    'Payable.payment_type_id' => array(2, 3, 4),
                                    'Payable.on_hold' => 0,
                                );
                if($mpf){ $condition['Payable.mpf_entitlement'] = $mpf;}
                $paymentTypeIDData = $this->Payable->find('all', array(
                                'conditions' => $condition,
                                'fields' => array('Payable.count', 'Payable.total_payable', 'Payable.payee_id_proof_no', 'Payable.payment_type_id'),
                                'group' => array('Payable.payment_type_id', 'Payable.payee_id_proof_no'),
                                'order' => array('Payable.payee_id_proof_no'),
                            ));
                
                $paymentData = array();
                if(!empty($paymentTypeIDData)){
                    foreach ($paymentTypeIDData as $data){
                        $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['count'] = $data['Payable']['count'];
                        $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['total_payable'] = $data['Payable']['total_payable'];
                    }
                }
                $this->set('paymentData', $paymentData);
                
                $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payee_id_proof_no)', 'total_payable' => 'SUM(Payable.amount_payable)');
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payee.joining_date >= ' => $join_date_min,
                        'Payee.joining_date <= ' => $join_date_max,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payee.joining_date', 'Payable.payee_id_proof_no', 'Payable.count', 'Payable.bank_account_no', 'Payable.total_payable', 'Payable.mpf_entitlement'),
                    'group' => array('Payable.payee_id_proof_no'),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                $element_to_render = 'calculations';
                
                $textLabel = __('MPF_N_PAYMENT', true);
                break;
            case 3:
                $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'month' => "DATE_FORMAT(Payable.created, '%m')");
                $sumData = $this->Payable->find('all', array(
                                'conditions' => array(
                                    'Payable.created >= ' => date('Y-m-d', strtotime($filterDateMin . " -5 month")),
                                    'Payable.created <= ' => $filterDateMax,
                                    'Payable.on_hold' => 0,
                                ),
                                'fields' => array('Payable.payee_id_proof_no', 'Payable.total_payable', 'Payable.month'),
                                'group' => array("DATE_FORMAT(Payable.created, '%Y-%m')", 'Payable.payee_id_proof_no' ),
                            ));
                $finalData = array();
                if(!empty($sumData)){
                    foreach($sumData as $details){
                        $finalData[$details['Payable']['payee_id_proof_no']][$details['Payable']['month']] = $details['Payable']['total_payable'];
                    }
                }
                $this->set('finalData', $finalData);
                
                $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.created >= ' => date('Y-m-d', strtotime($filterDateMin . " -5 month")),
                        'Payable.created <= ' => $filterDateMax,
                        'Payable.on_hold' => 0,
                    ),
                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no'),
                    'group' => array("Payable.payee_id_proof_no"),
                );
                if($mpf){ $this->Paginator->settings['conditions']['Payable.mpf_entitlement'] = $mpf;}
                
                $textLabel = __('MPF_N_PAYMENT_SIX_MONTH', true);
                $element_to_render = 'six_month_report';
                break;
        }
        
        $mpfLabel = 'Both';
        if($mpf == 'Y'){$mpfLabel = 'Yes';}
        if($mpf == 'N'){$mpfLabel = 'No';}
        
        $this->set('mpfLabel', $mpfLabel);
        $this->set('textLabel', $textLabel);
        $this->set('switch', $switch);
        $payableList = $this->Paginator->paginate('Payable');
        $this->set('payableList', $payableList);
        //pr($payableList);
        $this->set('mpf', $mpf);
        $this->set('Month', $Month);
        $this->set('Year', $Year);
        
        $this->set('filterDateMin', $filterDateMin);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'reports';
            $this->render($element_to_render, 'ajax');
        }
    }
    
    /* __________________________________________________________
     * @Method      :view_details
     * @Description :to provide a Payable sorting interface
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function view_details($payee_id = null, $switch = null){
        $this->autoRender = false;
        $this->layout = '';
        $group = array();
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->unBindModel(array('belongsTo' => array('BatchList')) ,false);
        $this->Payable->bindModel(array(
            'hasOne' => array(
                'Payee' => array(
                    'foreignKey' => false,
                    'conditions' => 'Payee.payee_id_proof_no = Payable.payee_id_proof_no'
                )
            )
        ), false);
        
        switch ($switch) {
            case 1:
                $group = array('Payable.bank_account_no');
                break;
            case 2:
                $group = array('Payable.e_name');
                break;
            case 3:
            case 4:
            case 5:
                $group = array();
                break;
            case 6:
            case 8:
                $group = array('Payee.bank_account_no');
                break;
        }
        
        
        $this->Paginator->settings = array(
            'conditions' => array('Payee.id' => $payee_id),
            'fields' => array('Payable.id', 'Payable.batch_list_id', 'Payable.payee_id_proof_no', 'Payable.e_name', 'Payable.mpf_entitlement', 'Payable.amount_payable', 'Payee.bank_account_no', 'Payable.on_hold'),
            'group' => $group,
        );
        $payableDetailList = $this->Paginator->paginate('Payable');
        
        //------Batch Unit Details-----------
        $batchListData = array();
        App::Import("Model", "BatchList");
        $this->BatchList = new BatchList();
        $this->BatchList->bindModel(array(
            'hasOne' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $this->BatchList->recursive = 0;
        $batchListArray = $this->BatchList->find('all', array('fields' => array('BatchList.id', 'BatchList.unit_code', 'ServiceUnit.unit_name')));
        
        if(!empty($batchListArray)){
            $i = 0;
            foreach($batchListArray as $batchData){
                $batchListData[$batchData['BatchList']['id']]['unit_code'] = $batchData['BatchList']['unit_code'];
                $batchListData[$batchData['BatchList']['id']]['unit_name'] = $batchData['ServiceUnit']['unit_name'];
                $i++;
            }
        }
        
        $this->set('batchListData', $batchListData);
        $this->set('payableDetailList', $payableDetailList);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'reports';
            $this->render('list_detail', 'ajax');
        }//if
    }
    
    /* __________________________________________________________
     * @Method      :download_report
     * @Description :to download reports
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function download_report() {
        $userId   = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        $this->autoRender = false;
        $this->layout = '';
        if($this->request->data){
            //pr($this->request->data);
            $print_version = 'xls';
            if(isset($this->request->data['print_pdf'])){
                $print_version = 'pdf';
            }
            
            $month = $this->request->data['Report']['month'];
            $year = $this->request->data['Report']['year'];
            $mpf = $this->request->data['Report']['mpf'];
            $switch = $this->request->data['Report']['switch'];
            
            if (!$month || !$year) {
                echo "Wrong Values Passed";
                die;
            }

            $filterDate = $filterDateMin = $filterDateMax = '';
            $filterDate = $year.'-'.$month;
            $maxDate = date("t", strtotime($filterDate));
            $filterDateMin = $year.'-'.$month.'-1';
            $filterDateMax = $year.'-'.$month.'-'.$maxDate;


            App::Import("Model", "Payable");
            $this->Payable = new Payable();
            $this->Payable->unBindModel(array('belongsTo' => array('BatchList')) ,false);
            $this->Payable->bindModel(array(
                'hasOne' => array(
                    'Payee' => array(
                        'foreignKey' => false,
                        'conditions' => 'Payee.payee_id_proof_no = Payable.payee_id_proof_no'
                    )
                )
            ), false);
            
            switch ($switch) {
                case 11:
                    $this->Payable->virtualFields = array('count' => 'COUNT(DISTINCT Payable.bank_account_no)');
                    $conditions = array(
                                        'Payable.created >= ' => $filterDateMin,
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $conditions['Payable.mpf_entitlement'] = $mpf;}
                    
                    $payableList = $this->Payable->find('all', array(
                                    'conditions' => $conditions,
                                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                                    'group' => array('Payable.payee_id_proof_no HAVING COUNT(DISTINCT Payable.bank_account_no) > 1'),
                                ));
                    $this->prepareGeneralResult($payableList, $print_version, 'more_than_one_account');
                    
                    break;
                case 12:
                    $this->Payable->virtualFields = array('count' => 'COUNT(DISTINCT Payable.e_name)');
                    $conditions = array(
                                        'Payable.created >= ' => $filterDateMin,
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $conditions['Payable.mpf_entitlement'] = $mpf;}
                    
                    $payableList = $this->Payable->find('all', array(
                                        'conditions' => $conditions,
                                        'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                                        'group' => array('Payable.payee_id_proof_no HAVING COUNT(DISTINCT Payable.e_name) > 1'),
                                    ));
                    $this->prepareGeneralResult($payableList, $print_version, 'different_english_name');
                    break;
                case 13:
                    $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payee_id_proof_no)');
                    $conditions = array(
                                        'Payable.created >= ' => $filterDateMin,
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $conditions['Payable.mpf_entitlement'] = $mpf;}
                    
                    $payableList = $this->Payable->find('all', array(
                                        'conditions' => $conditions,
                                        'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no', 'Payable.count'),
                                        'group' => array('Payable.payee_id_proof_no HAVING COUNT(Payable.payee_id_proof_no) > 1'),
                                    ));
                    $this->prepareGeneralResult($payableList, $print_version, 'more_than_one_payable');
                    break;
                case 17:
                    $conditions = array(
                                        'Payable.created >= ' => $filterDateMin,
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payee.joining_date' => '0000-00-00',
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $conditions['Payable.mpf_entitlement'] = $mpf;}
                    
                    $payableList = $this->Payable->find('all', array(
                                        'conditions' => $conditions,
                                        'fields' => array('Payee.id', 'Payee.joining_date', 'Payee.eng_name', 'Payable.payee_id_proof_no'),
                                        'group' => array('Payable.payee_id_proof_no'),
                                    ));
                    $this->prepareGeneralResult($payableList, $print_version, 'no_joinig_date');
                    break;
                case 21:
                    $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payment_type_id)', 'total_payable' => 'SUM(Payable.amount_payable)');
                    $condition = array(
                                        'Payable.created >= ' => $filterDateMin,
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payable.payment_type_id' => array(2, 3, 4),
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $condition['Payable.mpf_entitlement'] = $mpf;}
                    $paymentTypeIDData = $this->Payable->find('all', array(
                                    'conditions' => $condition,
                                    'fields' => array('Payable.count', 'Payable.total_payable', 'Payable.payee_id_proof_no', 'Payable.payment_type_id'),
                                    'group' => array('Payable.payment_type_id', 'Payable.payee_id_proof_no'),
                                    'order' => array('Payable.payee_id_proof_no'),
                                ));

                    $paymentData = array();
                    if(!empty($paymentTypeIDData)){
                        foreach ($paymentTypeIDData as $data){
                            $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['count'] = $data['Payable']['count'];
                            $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['total_payable'] = $data['Payable']['total_payable'];
                        }
                    }
                    
                    $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payee_id_proof_no)', 'total_payable' => 'SUM(Payable.amount_payable)');
                    $conditions = array(
                                        'Payable.created >= ' => $filterDateMin,
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $conditions['Payable.mpf_entitlement'] = $mpf;}
                    $payableList = $this->Payable->find('all', array(
                                    'conditions' => $conditions,
                                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payee.joining_date', 'Payable.payee_id_proof_no', 'Payable.count', 'Payable.bank_account_no', 'Payable.total_payable', 'Payable.mpf_entitlement'),
                                    'group' => array('Payable.payee_id_proof_no'),
                                ));
                    
                    $this->prepareResult($paymentData, $payableList, $mpf, $print_version, 'monthly_part_time_payroll_report', $filterDateMin);
                    break;
                case 22:
                    $mpf_plus_thirty = date('Y-m-d', strtotime($filterDateMin . " -1 month"));
                    $join_date_min = date('Y-m-d', strtotime($mpf_plus_thirty . " -30 day"));
                    $join_date_month = date('m', strtotime($join_date_min));
                    $join_date_year = date('Y', strtotime($join_date_min));
                    $join_day_max = date("t", strtotime($join_date_year . '-' . $join_date_month));
                    $join_date_max = $join_date_year . '-' . $join_date_month . '-' . $join_day_max;

                    $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payment_type_id)', 'total_payable' => 'SUM(Payable.amount_payable)');
                    $condition = array(
                                        'Payee.joining_date >= ' => $join_date_min,
                                        'Payee.joining_date <= ' => $join_date_max,
                                        'Payable.payment_type_id' => array(2, 3, 4),
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $condition['Payable.mpf_entitlement'] = $mpf;}
                    $paymentTypeIDData = $this->Payable->find('all', array(
                                    'conditions' => $condition,
                                    'fields' => array('Payable.count', 'Payable.total_payable', 'Payable.payee_id_proof_no', 'Payable.payment_type_id'),
                                    'group' => array('Payable.payment_type_id', 'Payable.payee_id_proof_no'),
                                    'order' => array('Payable.payee_id_proof_no'),
                                ));

                    $paymentData = array();
                    if(!empty($paymentTypeIDData)){
                        foreach ($paymentTypeIDData as $data){
                            $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['count'] = $data['Payable']['count'];
                            $paymentData[$data['Payable']['payee_id_proof_no']][$data['Payable']['payment_type_id']]['total_payable'] = $data['Payable']['total_payable'];
                        }
                    }
                    
                    $this->Payable->virtualFields = array('count' => 'COUNT(Payable.payee_id_proof_no)', 'total_payable' => 'SUM(Payable.amount_payable)');
                    $conditions = array(
                                        'Payee.joining_date >= ' => $join_date_min,
                                        'Payee.joining_date <= ' => $join_date_max,
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $conditions['Payable.mpf_entitlement'] = $mpf;}
                    $payableList = $this->Payable->find('all', array(
                                    'conditions' => $conditions,
                                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payee.joining_date', 'Payable.payee_id_proof_no', 'Payable.count', 'Payable.bank_account_no', 'Payable.total_payable', 'Payable.mpf_entitlement'),
                                    'group' => array('Payable.payee_id_proof_no'),
                                ));
                    
                    $this->prepareResult($paymentData, $payableList, $mpf, $print_version, 'monthly_mpf_payment_report', $filterDateMin);
                    break;
                case 23:
                    $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'month' => "DATE_FORMAT(Payable.created, '%m')");
                    $sumData = $this->Payable->find('all', array(
                                    'conditions' => array(
                                        'Payable.created >= ' => date('Y-m-d', strtotime($filterDateMin . " -5 month")),
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payable.on_hold' => 0,
                                    ),
                                    'fields' => array('Payable.payee_id_proof_no', 'Payable.total_payable', 'Payable.month'),
                                    'group' => array("DATE_FORMAT(Payable.created, '%Y-%m')", 'Payable.payee_id_proof_no' ),
                                ));
                    $finalData = array();
                    if(!empty($sumData)){
                        foreach($sumData as $details){
                            $finalData[$details['Payable']['payee_id_proof_no']][$details['Payable']['month']] = $details['Payable']['total_payable'];
                        }
                    }

                    $conditions = array(
                                        'Payable.created >= ' => date('Y-m-d', strtotime($filterDateMin . " -5 month")),
                                        'Payable.created <= ' => $filterDateMax,
                                        'Payable.on_hold' => 0,
                                    );
                    if($mpf){ $conditions['Payable.mpf_entitlement'] = $mpf;}
                    $payableList = $this->Payable->find('all', array(
                                    'conditions' => $conditions,
                                    'fields' => array('Payee.id', 'Payee.eng_name', 'Payable.payee_id_proof_no'),
                                    'group' => array('Payable.payee_id_proof_no'),
                                ));
                    
                    $this->prepareResultSixMonth($finalData, $payableList, $mpf, $print_version, 'employer_mpf_payment_records', $filterDateMin);
                    break;
            }
        }
    }
    
    /* __________________________________________________________
     * @Method      :prepareResultSixMonth
     * @Description :to generate html of reports
     * @access      :null
     * @param      :null
     * @return     :null*/
    public function prepareGeneralResult($payableList = array(), $print_version = 'xls', $file_name = ''){
        
        $html = '<table width="100%" border="1" cellspacing="0" cellpadding="0">
                    <tr class="heading"  bgcolor="#708089">
                        <th valign="top"  align ="left">' . __('ENG_NAME', true) . '</th>
                        <th valign="top"  align ="left">' . __('PAYEE_ID_PROOF_NO', true) . '</th>
                        <th valign="top"  align ="left">' . __('COUNT', true) . '</th>
                    </tr>';
                    if (isset($payableList) && count($payableList) > 0) {
                        foreach ($payableList as $arrayDetail) {
                            $count = isset($arrayDetail['Payable']['count']) ? $arrayDetail['Payable']['count']. ' ' .__('ITEM_FOUND') : '--';
                            $html .= '<tr>
                                        <td>' . $arrayDetail['Payee']['eng_name'] . '</td>
                                        <td>' . $arrayDetail['Payable']['payee_id_proof_no'] . '</td>
                                        <td>' . $count. '</td>
                                    </tr>';      
                        }
                    }
                $html .= '</table>';
 
        $this->printResult($html, $print_version, $file_name);
    }
    
    /* __________________________________________________________
     * @Method      :prepareResultSixMonth
     * @Description :to generate html of reports
     * @access      :null
     * @param      :null
     * @return     :null*/
    public function prepareResultSixMonth($finalData = array(), $payableList = array(), $mpf = '', $print_version = 'xls', $file_name = '', $filterDateMin = ''){
        
        $html = '<table width="100%" border="1" cellspacing="0" cellpadding="0">
                    <tr class="heading"  bgcolor="#708089">
                        <th valign="top"  align ="left">' . __('PAYEE', true) . '</th>
                        <th valign="top"  align ="left">' . __('PAYEE_ID_PROOF_NO', true) . '</th>
                        <th valign="top"  align ="left">' . date('M-y', strtotime($filterDateMin. " -5 month")) . '</th>
                        <th valign="top"  align ="left">' . date('M-y', strtotime($filterDateMin. " -4 month")) . '</th>
                        <th valign="top"  align ="left">' . date('M-y', strtotime($filterDateMin. " -3 month")) . '</th>
                        <th valign="top"  align ="left">' . date('M-y', strtotime($filterDateMin. " -2 month")) . '</th>
                        <th valign="top"  align ="left">' . date('M-y', strtotime($filterDateMin. " -1 month")) . '</th>
                        <th valign="top"  align ="left">' . date('M-y', strtotime($filterDateMin)) . '</th>
                    </tr>';
                    if (isset($payableList) && count($payableList) > 0) {
                        foreach ($payableList as $arrayDetail) {
                            $fifth = isset($finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -5 month"))]) ? $finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -5 month"))] : '';
                            $fourth = isset($finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -4 month"))]) ? $finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -4 month"))] : '';
                            $third = isset($finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -3 month"))]) ? $finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -3 month"))] : '';
                            $second = isset($finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -2 month"))]) ? $finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -2 month"))] : '';
                            $first = isset($finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -1 month"))]) ? $finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin. " -1 month"))] : '';
                            $itself = isset($finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin))]) ? $finalData[$arrayDetail['Payable']['payee_id_proof_no']][date('m', strtotime($filterDateMin))] : '';
                    
                            $html .= '<tr>
                                        <td>' . $arrayDetail['Payee']['eng_name'] . '</td>
                                        <td>' . $arrayDetail['Payable']['payee_id_proof_no'] . '</td>
                                        <td>' . $fifth. '</td>
                                        <td>' . $fourth . '</td>
                                        <td>' . $third . '</td>
                                        <td>' . $second . '</td>
                                        <td>' . $first . '</td>
                                        <td>' . $itself . '</td>
                                    </tr>';      
                        }
                    }
                $html .= '</table>';
 
        $this->printResult($html, $print_version, $file_name);
    }
    
    /* __________________________________________________________
     * @Method      :prepareResult
     * @Description :to generate html of reports
     * @access      :null
     * @param      :null
     * @return     :null*/
    public function prepareResult($paymentData = array(), $payableList = array(), $mpf = '', $print_version = 'xls', $file_name = '', $filterDateMin = ''){
        
        $html = '<table width="100%" border="1" cellspacing="0" cellpadding="0">
                    <tr class="heading"  bgcolor="#708089">
                        <th valign="top"  align ="left">' . __('PAYEE', true) . '</th>
                        <th valign="top"  align ="left">' . __('PAYEE_ID_PROOF_NO', true) . '</th>
                        <th valign="top"  align ="left">' . __('CNT', true) . '</th>
                        <th valign="top"  align ="left">' . __('BANK_ACCOUNT_NO', true) . '</th>
                        <th valign="top"  align ="left">' . __('JOINING_DATE', true) . '</th>
                        <th valign="top"  align ="left">' . __('JOINING_DATE_PLUS_THIRTY', true) . '</th>
                        <th valign="top"  align ="left">' . __('MPF_START', true) . '</th>
                        <th valign="top"  align ="left">' . __('GROSS_PAYABLE', true) . '</th>';if($mpf != 'N'){$html .= 
                        '<th valign="top"  align ="left">' . __('EMPLOYEE_MPF', true) . '</th>
                        <th valign="top"  align ="left">' . __('EMPLOYER_MPF', true) . '</th>';} $html .=        
                        '<th valign="top"  align ="left">' . __('NET_PAYABLE', true) . '</th>';if($mpf != 'N'){$html .= 
                        '<th valign="top"  align ="left">' . __('MONTH_TO_PAY_MPF', true) . '</th>';} $html .= 
                        '<th valign="top"  align ="left">' . __('CONTRACT', true) . '</th>
                        <th valign="top"  align ="left">' . __('CONTRACT_COUNT', true) . '</th>
                        <th valign="top"  align ="left">' . __('REPLACEMENT', true) . '</th>
                        <th valign="top"  align ="left">' . __('REPLACEMENT_COUNT', true) . '</th>
                        <th valign="top"  align ="left">' . __('REIMBURSE', true) . '</th>
                        <th valign="top"  align ="left">' . __('REIMBURSE_COUNT', true) . '</th>
                        <th valign="top"  align ="left">' . __('GRAND_TOTAL', true) . '</th>
                    </tr>';
                    $totalPayableAll = $totalEmployeeMPF = $totalEmployerMPF = $grandTotalAll = $payment_id_two_count_all = $payment_id_three_count_all = $payment_id_four_count_all = $payment_id_two_contract_count_all = $payment_id_three_contract_count_all = $payment_id_four_contract_count_all = 0;
                    if (isset($payableList) && count($payableList) > 0) {
                        foreach ($payableList as $arrayDetail) {
                            
                            $employeeMPF = $employerMPF = 0;
                            $mpf_start_date = $join_date_plus_thirty = $month_to_pay_mpf = '';

                            if($arrayDetail['Payable']['total_payable']){
                                $employerMPF = (($arrayDetail['Payable']['total_payable'] * 5) / 100);
                                $employerMPF = ceil($employerMPF);
                                if($arrayDetail['Payable']['total_payable'] > 7100){
                                    $employeeMPF = ceil($employerMPF);
                                }
                            }
                            if($arrayDetail['Payee']['joining_date'] != '0000-00-00'){
                                $join_date_plus_thirty = date('M j, Y', strtotime($arrayDetail['Payee']['joining_date'] . " +30 days"));

                                $join_date_plus_thirty_month = date("m", strtotime($join_date_plus_thirty));
                                $join_date_plus_thirty_year = date("Y", strtotime($join_date_plus_thirty));
                                $join_date_plus_thirty_min_date = $join_date_plus_thirty_year.'-'.$join_date_plus_thirty_month.'-1';
                                $mpf_start_date = date('M j, Y', strtotime($join_date_plus_thirty_min_date . " +1 month"));

                                $month_to_pay_mpf = date('M j, Y', strtotime($mpf_start_date . " +1 month"));
                            }
                            if($employerMPF > 1500){
                                $employeeMPF = $employerMPF = 1500;
                            }
                            
                            if($mpf == 'N'){$employeeMPF = $employerMPF = 0;}
                            if($mpf != 'N'){
                                if(strtotime($month_to_pay_mpf) < strtotime($filterDateMin)){
                                    $month_to_pay_mpf = date('M j, Y', strtotime($filterDateMin . " +1 month"));
                                }
                            }
                            
                            //----Payment calculation -----
                            $payment_id_two_contract_count = isset($paymentData[$arrayDetail['Payable']['payee_id_proof_no']][2]) ? $paymentData[$arrayDetail['Payable']['payee_id_proof_no']][2]['count'] : 0; 
                            $payment_id_two_count = isset($paymentData[$arrayDetail['Payable']['payee_id_proof_no']][2]) ? $paymentData[$arrayDetail['Payable']['payee_id_proof_no']][2]['total_payable'] : 0; 
                            $payment_id_three_contract_count = isset($paymentData[$arrayDetail['Payable']['payee_id_proof_no']][3]) ? $paymentData[$arrayDetail['Payable']['payee_id_proof_no']][3]['count'] : 0; 
                            $payment_id_three_count = isset($paymentData[$arrayDetail['Payable']['payee_id_proof_no']][3]) ? $paymentData[$arrayDetail['Payable']['payee_id_proof_no']][3]['total_payable'] : 0; 
                            $payment_id_four_contract_count = isset($paymentData[$arrayDetail['Payable']['payee_id_proof_no']][4]) ? $paymentData[$arrayDetail['Payable']['payee_id_proof_no']][4]['count'] : 0; 
                            $payment_id_four_count = isset($paymentData[$arrayDetail['Payable']['payee_id_proof_no']][4]) ? $paymentData[$arrayDetail['Payable']['payee_id_proof_no']][4]['total_payable'] : 0; 
                            
                            $totalPayableAll = $totalPayableAll + $arrayDetail['Payable']['total_payable'];
                            $totalEmployeeMPF = $totalEmployeeMPF + $employeeMPF;
                            $totalEmployerMPF = $totalEmployerMPF + $employerMPF;
                            $grandTotal = ($arrayDetail['Payable']['total_payable'] - $employeeMPF + $payment_id_two_count + $payment_id_four_count + $payment_id_four_count);
                            $grandTotalAll = $grandTotalAll + $grandTotal;

                            $payment_id_two_count_all = $payment_id_two_count_all + $payment_id_two_count;
                            $payment_id_three_count_all = $payment_id_three_count_all + $payment_id_three_count;
                            $payment_id_four_count_all = $payment_id_four_count_all + $payment_id_four_count;
                            
                            $payableCount = isset($arrayDetail['Payable']['count']) ? $arrayDetail['Payable']['count'] : '--' ;
                            $joiningDate = ($arrayDetail['Payee']['joining_date'] != '0000-00-00') ? date('M j, Y', strtotime($arrayDetail['Payee']['joining_date'])) : __('NOT_AVAILABLE');
                            $html .= '<tr>
                                        <td>' . $arrayDetail['Payee']['eng_name'] . '</td>
                                        <td>' . $arrayDetail['Payable']['payee_id_proof_no'] . '</td>
                                        <td>' . $payableCount. '</td>
                                        <td>' . $arrayDetail['Payable']['bank_account_no'] . '</td>
                                        <td>' . $joiningDate . '</td>
                                        <td>' . $join_date_plus_thirty . '</td>
                                        <td>' . $mpf_start_date . '</td>
                                        <td>' . $arrayDetail['Payable']['total_payable'] . '</td>';if($mpf != 'N'){$html .= 
                                        '<td>' . $employeeMPF . '</td>
                                        <td>' . $employerMPF . '</td>';} $html .=    
                                        '<td>' . ($arrayDetail['Payable']['total_payable'] - $employeeMPF) . '</td>';if($mpf != 'N'){$html .= 
                                        '<td>' . $month_to_pay_mpf . '</td>';} $html .=  
                                        '<td>' . $payment_id_two_count . '</td>
                                        <td>' . $payment_id_two_contract_count . '</td>
                                        <td>' . $payment_id_three_count . '</td>
                                        <td>' . $payment_id_three_contract_count . '</td>
                                        <td>' . $payment_id_four_count . '</td>
                                        <td>' . $payment_id_four_contract_count . '</td>
                                        <td>' . $grandTotal . '</td>
                                    </tr>';      
                        }
                        $html .= '<tr>
                            <td><strong>' . __('REPORT_TOTAL', true) . ' : </strong></td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>' . $totalPayableAll . '</td>';if($mpf != 'N'){$html .= 
                            '<td>' . $totalEmployeeMPF . '</td>
                            <td>' . $totalEmployerMPF . '</td>';} $html .=    
                            '<td>' . ($totalPayableAll - $totalEmployeeMPF) . '</td>
                            <td>&nbsp;</td>
                            <td>' . $payment_id_two_count_all . '</td>
                            <td>' . $payment_id_two_contract_count_all . '</td>
                            <td>' . $payment_id_three_count_all . '</td>
                            <td>' . $payment_id_three_contract_count_all . '</td>
                            <td>' . $payment_id_four_count_all . '</td>
                            <td>' . $payment_id_four_contract_count_all . '</td>
                            <td>' . $grandTotalAll . '</td>
                        </tr>';
                    }
                $html .= '</table>';
 
        $this->printResult($html, $print_version, $file_name);
    }
    
    /* __________________________________________________________
     * @Method      :printResult
     * @Description :to make output in downloadable format
     * @access      :null
     * @param      :null
     * @return     :null*/
    public function printResult($content = '', $print_version = null, $file_name = null){
        Configure::write('debug', 0);
        App::import('Vendor', 'PHPExcel', array('file' => 'PHPExcel'.DS.'Classes'.DS.'PHPExcel.php'));
        App::import('Vendor', 'TCPDF', array('file' => 'TCPDF'.DS.'tcpdf.php'));
        //------------Excel File creation starts here--------------------
        if($print_version == 'xls'){
            // Put the html into a temporary file
            $tmpfile = time().'.html';
            file_put_contents($tmpfile, $content);

            // Read the contents of the file into PHPExcel Reader class
            $objPHPExcel = PHPExcel_IOFactory::load($tmpfile);
            // Pass to writer and output as needed
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            
            // We'll be outputting an excel file
            header('Content-type: application/vnd.ms-excel');
            // It will be called file.xls
            header('Content-Disposition: attachment; filename="'.$file_name.'.xls"');
            
            // Write file to the browser
            $objWriter->save('php://output');

            // Delete temporary file
            //unlink(TEMP_DIR.DS.$tmpfile);
        }
        if($print_version == 'pdf'){
            $pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
            $pdf->setPrintHeader(false);
            $pdf->setPrintFooter(false);
            
            // set document information
            $pdf->SetCreator(PDF_CREATOR);
            $pdf->SetAuthor('Admin');
            $pdf->SetTitle('Monthly Part Time Payroll Report');

            $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

            // set margins
            $pdf->SetMargins(PDF_MARGIN_LEFT, 10, PDF_MARGIN_RIGHT);

            // set auto page breaks
            $pdf->SetAutoPageBreak(TRUE, 5);

            // set image scale factor
            $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

            // set some language-dependent strings (optional)
            if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
                require_once(dirname(__FILE__).'/lang/eng.php');
                $pdf->setLanguageArray($l);
            }

            // ---------------------------------------------------------

            // set default font subsetting mode
            $pdf->setFontSubsetting(true);

            // Set font
            // dejavusans is a UTF-8 Unicode font, if you only need to
            // print standard ASCII chars, you can use core fonts like
            // helvetica or times to reduce file size.
            $pdf->SetFont('kozminproregular', '', 9, '', true);
            
            // Add a page
            // This method has several options, check the source code documentation for more information.
            $pdf->AddPage();
            $pdf->writeHTML($content, true, false, true, false, '');
            $pdf->lastPage();
            $pdf->Output($file_name.'.pdf', 'D');
        }
    }
    
    public function edit_payable($id = null){
        $this->layout = 'pop_up_window';
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        if (!$id) {
            throw new NotFoundException(__('INVALID_PAYEE'));
        }
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->bindModel(array(
            'hasOne' => array(
                'Payee' => array(
                    'foreignKey' => false,
                    'conditions' => 'Payee.payee_id_proof_no = Payable.payee_id_proof_no'
                )
            )
        ), false);
        
        $payable = $this->Payable->findById($id);

        if (!$payable) {
            throw new NotFoundException(__('INVALID_PAYEE'));
        }
        
        if ($this->request->is('ajax')) {
            $this->disableCache();
            $this->Payable->id = $id;
            
            $batch_list_id = $this->request->data['Payable']['batch_list_id'];
            if ($this->Payable->save($this->request->data)) {
                
                $this->Payable->virtualFields = array('total' => 'SUM(Payable.amount_payable)');
                $payableTotal = $this->Payable->find('first', array(
                        'conditions' => array('Payable.batch_list_id' => $batch_list_id),
                        'fields' => array('Payable.total')
                    ));
                
                App::Import("Model", "BatchList");
                $this->BatchList = new BatchList();
                $batchData = array();
                $batchData['BatchList']['id'] = $batch_list_id;
                $batchData['BatchList']['total_amount'] = $payableTotal['Payable']['total'];
                $this->BatchList->save($batchData);
                
                App::Import("Model", "Payee");
                $this->Payee = new Payee();
                $this->Payee->validator()->remove('eng_name');
                $this->Payee->validator()->remove('chi_name');
                $this->Payee->validator()->remove('phone');
                $this->Payee->validator()->remove('email');
                $this->Payee->validator()->remove('bank_account_no');
                
                $payeeData['Payee'] = array(
                        'id' => $this->request->data['Payee']['id'],
                        'joining_date' => $this->request->data['Payee']['joining_date'],
                        'bank_account_no' => $this->request->data['Payee']['bank_account_no']
                    );
                $this->Payee->save($payeeData);
                
                $msg = __("PAYEE_UPDATE_SUCCESS", true);
                $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
                //return $this->redirect(array('action' => 'admin_verification_view/' . $batch_list_id));
            } else{
                $msg = __("PAYEE_UPDATE_FAILURE", true);
                $this->Session->setFlash('<div class="totalErrors">' . $msg . '</div>');
                //return $this->redirect(array('action' => 'view/' . $batch_list_id));
            }
            $payable = $this->Payable->findById($id);
            $this->request->data = $payable;
            $this->viewPath = 'Reports';
            $this->render('edit_payable', 'ajax');
        }
        if (!$this->request->data) {
            $this->request->data = $payable;
            $this->request->data['Payable']['batch_list_id'] = $payable['BatchList']['id'];
        }
    }
    
    public function payment_history($payee_id_proof_no = null) {
        $this->layout = 'pop_up_window';
        $this->Payable->virtualFields = array(
                                            'count' => 'COUNT(Payable.payee_id_proof_no)', 
                                            'year_month' => "DATE_FORMAT(Payable.created, '%Y%m')", 
                                            'year' => "DATE_FORMAT(Payable.created, '%Y')", 
                                            'month' => "DATE_FORMAT(Payable.created, '%m')", 
                                            'total_for_month_income' => 'SUM(Payable.total_fee_income)',
                                            'total_payable' => 'SUM(Payable.amount_payable)'
                                        );
        $conditions = array('Payable.payee_id_proof_no' => $payee_id_proof_no);
        
        $payableList = $this->Payable->find('all', array(
                        'conditions' => $conditions,
                        'fields' => array('Payable.payee_id_proof_no', 'Payable.e_name', 'Payable.year_month', 'Payable.count', 'Payable.year', 'Payable.month', 'Payable.total_payable', 'Payable.total_for_month_income'),
                        'group' => array('Payable.payee_id_proof_no', 'Payable.year_month'),
                        'order' => array('Payable.year_month DESC'),
                        'limit' => 12
                    ));
                    
        //pr($payableList);
        $this->set('payableList', $payableList);
    }
    
    public function payment_view($payee_id_proof_no = null, $year = null, $month = null) {
        $this->layout = 'pop_up_window';
        $filterDate = $year.'-'.$month;
        $maxDate = date("t", strtotime($filterDate));
        $filterDateMin = $year.'-'.$month.'-1';
        $filterDateMax = $year.'-'.$month.'-'.$maxDate;
        
        $this->Paginator->settings = array(
                    'conditions' => array(
                        'Payable.payee_id_proof_no' => $payee_id_proof_no, 
                        'Payable.created >= ' => $filterDateMin,
                        'Payable.created <= ' => $filterDateMax
                    ),
                    'fields' => array('BatchList.unit_code', 'Payable.e_name', 'Payable.payment_type_id', 'Payable.amount_payable', 'Payable.mpf_entitlement', 'Payable.income_splite_remarks', 'Payable.course_remarks', 'Payable.total_fee_income', 'Payable.course_head_count'),
                );
        $payableList = $this->Paginator->paginate('Payable');
 
        $this->set('payableList', $payableList);
    }
    
    public function excel_files() {
        $excel_file_array = array();
        $excel_file_array = $this->check_files(EXCEL_DIR);
        $this->set('excel_file_array', $excel_file_array);
    }
    
    public function help_files() {
        $help_file_array = array();
        $help_file_array = $this->check_files(HELP_DIR);
        $this->set('help_file_array', $help_file_array);
    }
    
    public function download($file_name = null, $dir_name = null) {
        $path = HELP_DIR;
        if($dir_name == 'excel_file'){
            $path = EXCEL_DIR;
        } 
        header("Content-Type: application/octet-stream");
        header("Content-Transfer-Encoding: Binary");
        header("Content-disposition: attachment; filename=".str_replace(' ', "_", $file_name)); 
        echo readfile($path.'/'.$file_name);
    }
    
    public function check_files($path, &$files = array()){
        $dir = opendir($path."/.");
        while($item = readdir($dir))
        if(is_file($sub = $path."/".$item))
        $files[] = $item;
        else
        if($item != "." and $item != "..")
        files($sub, $files);
        return($files);
    }
}